Release 10.1A: OpenEdge Reporting:
Query/Results for Windows

Types of table relationships

Results contains two types of table relationships:

Partial

When you create a query, Results automatically establishes a partial relationship between each pair of tables in the query. A partial relationship, also called an inner join, accesses only those records in the first table that have related records in the second table.

Complete

If you want to access all the records in the first table whether or not they have related records in the second table, change the relationship to a complete relationship. A complete relationship is also called an outer join.

If you set a complete relationship between a pair of tables and use sort ordering to control the order in which the query retrieves records, Results automatically uses the primary index in the first table in the relationship as the first sort field. See the "Sorting records" section for more information.

For example, you might have a query that displays the invoices for your customers by invoice date, as shown in Figure 5–1.

Figure 5–1: Example of a partial relationship

If you change the table relationship for the query presented in Figure 5–1 to a complete relationship, Results will automatically use the Cust-Num field (primary index in the first table) then the Invoice-Date field to sort the query. Thus the query will display the records by invoice date for each customer.

Note that the values for aggregate subtotals and totals only associated with fields from the second table will change to reflect the new sort order when you change the table relationship to a complete relationship. For example, if you created an aggregate total to total the amount of the invoices by date, then changed the relationship between the Customer and Invoice tables to a complete relationship, the aggregate total would then total the amount of the invoices by date for each customer.

See the "Record displays in multi-table queries" section for examples of partial and complete relationships.

To change the relationship between one or more pair of tables:

Choose Table Relationship Types. The Relationship Types dialog box appears:





Note: Partial relationships are displayed in white and complete relationships are displayed in reverse video.
To change the type of relationship between a pair of tables, simply select the pair of tables you want to modify. Results automatically toggles to the opposite relationship type.
When you are done modifying relationship types, choose OK.